﻿using System;
using System.IO;
using System.Linq;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Resources;
using System.Globalization;

using CE.User;
using CE.Global;
using CE.Database;
using CE.Report;

public partial class admin_sales_list : System.Web.UI.Page
{
    string PageCode = "GGRPTRADCRMSSL";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Session[CE.Global.General.UserId] == null)
        {
            Response.Redirect("~/ManagementSecureLogin.aspx");
        }

        if (!IsPostBack)
        {
            if (Convert.ToInt64(Session[General.UserId].ToString()) != 1)
            {
                CheckPageAccess();
            }
            //GenerateExcel();
            grvSalesList.Visible = false;
            lblError.Text = "";
            lblContent.Text = "";
            ddlDay1.Visible = true;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
            ddlYear1.Items.Clear();
            ddlYear2.Items.Clear();
            //DateConversion();

            int _cryr = CE.Global.General.GetUTCDateTime().Year;
            int _styr = 2010;
            for (int _year = _cryr; _year >= _styr; _year--)
            {
                ddlYear1.Items.Add(new ListItem(_year.ToString(), _year.ToString()));
                ddlYear2.Items.Add(new ListItem(_year.ToString(), _year.ToString()));
            }

            ddlDay1.Text = CE.Global.General.GetUTCDateTime().AddDays(-1).Day.ToString("00");
            ddlMonth1.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
            ddlYear1.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
            ddlDay2.Text = CE.Global.General.GetUTCDateTime().AddDays(-1).Day.ToString("00");
            ddlMonth2.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
            ddlYear2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
        }
    }

    protected void btnRefresh_Click(object sender, EventArgs e)
    {
        grvSalesList.Visible = false;
        lblError.Text = "";
        txtReportType.Text = ddlReportType.Text;
        txtAgentId.Text = Session[CE.Global.General.UserId].ToString();
        LoadGrid();
    }

    protected void ddlReportType_SelectedIndexChanged(object sender, EventArgs e)
    {
        lblError.Text = "";
        ddlDay1.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
        ddlDay2.Text = CE.Global.General.GetUTCDateTime().Day.ToString("00");
        ddlMonth1.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
        ddlMonth2.Text = CE.Global.General.GetUTCDateTime().Month.ToString("00");
        ddlYear1.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
        ddlYear2.Text = CE.Global.General.GetUTCDateTime().Year.ToString("0000");
        if (Convert.ToInt16(ddlReportType.Text) == 0)
        {
            ddlDay1.Visible = false;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 1)
        {
            ddlDay1.Visible = true;
            lblFromDate.Text = "Date :";
            lblToDate.Visible = false;
            ddlDay2.Visible = false;
            ddlMonth2.Visible = false;
            ddlYear2.Visible = false;
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 2)
        {
            lblFromDate.Text = "From Date :";
            lblToDate.Text = " To Date :";
            lblToDate.Visible = true;
            ddlDay2.Visible = true;
            ddlMonth2.Visible = true;
            ddlYear2.Visible = true;
            ddlDay1.Visible = true;
        }
    }

    protected void grvSalesList_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            e.Row.Cells[0].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[2].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[3].Attributes.CssStyle.Add("text-align", "center");
        }
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            e.Row.Cells[0].Attributes.CssStyle.Add("text-align", "center");
            e.Row.Cells[2].Attributes.CssStyle.Add("text-align", "right");
            e.Row.Cells[3].Attributes.CssStyle.Add("text-align", "center");

            Label lblSNo = (Label)e.Row.FindControl("lblSNo");
            string _SNo = DataBinder.Eval(e.Row.DataItem, "RefId").ToString().Trim();
            lblSNo.Text = _SNo.ToString();

            Label lblSalesPriority = (Label)e.Row.FindControl("lblSalesPriority");
            string _SalesPriority = DataBinder.Eval(e.Row.DataItem, "TotalName").ToString().Trim();
            lblSalesPriority.Text = _SalesPriority.ToString();
            if (_SalesPriority.ToUpper() == "DAILY TOTAL")
            {
                lblSalesPriority.Text = "GRAND TOTAL";
            }

            Label lblAmount = (Label)e.Row.FindControl("lblAmount");
            string _totalVMI = DataBinder.Eval(e.Row.DataItem, "Amount").ToString().Trim();
            float _fTotalVMI = 0.0f;
            if (_totalVMI != null && _totalVMI != string.Empty)
            {
                if (!float.TryParse(_totalVMI, out _fTotalVMI))
                {
                    _fTotalVMI = 0.0f;
                }
            }
            if (_fTotalVMI >= 1000)
            {
                lblAmount.Text = _fTotalVMI.ToString("#,000.00");
            }
            else if (_fTotalVMI < 1000 && _fTotalVMI > 0)
            {
                lblAmount.Text = _fTotalVMI.ToString("#0.00");
            }
            else
            {
                lblAmount.Text = "0.00";
            }

            GridView grid = (GridView)sender;

            if (e.Row.RowIndex == 12)
            {
                lblSNo.Text = "";
                e.Row.Cells[0].ForeColor = System.Drawing.Color.Green;
                e.Row.Cells[1].ForeColor = System.Drawing.Color.Green;
                e.Row.Cells[2].ForeColor = System.Drawing.Color.Green;
                e.Row.Cells[3].ForeColor = System.Drawing.Color.Green;

                e.Row.Cells[0].BackColor = System.Drawing.Color.Yellow;
                e.Row.Cells[1].BackColor = System.Drawing.Color.Yellow;
                e.Row.Cells[2].BackColor = System.Drawing.Color.Yellow;
                e.Row.Cells[3].BackColor = System.Drawing.Color.Yellow;

                e.Row.Cells[0].Font.Bold = true;
                e.Row.Cells[1].Font.Bold = true;
                e.Row.Cells[2].Font.Bold = true;
                e.Row.Cells[3].Font.Bold = true;
            }
        }
    }

    public bool ValidDate(Int32 Year, Int32 Month, Int32 Day)
    {
        bool _isValid = true;
        int daysInMonth = 0;
        string leap, monthValid, dayValid, yearValid;
        if (Year >= 0000)
        {
            yearValid = "yes";
        }
        else
        {
            yearValid = "no";
        }
        if (Year % 400 == 0)
        {
            leap = "yes";
        }
        else if (Year % 100 == 0)
        {
            leap = "no";
        }
        else if (Year % 4 == 0)
        {
            leap = "yes";
        }
        else
        {
            leap = "no";
        }

        switch (Month)
        {
            case 1: daysInMonth = 31;
                break;
            case 3: daysInMonth = 31;
                break;
            case 4: daysInMonth = 30;
                break;
            case 5: daysInMonth = 31;
                break;
            case 6: daysInMonth = 30;
                break;
            case 7: daysInMonth = 31;
                break;
            case 8: daysInMonth = 31;
                break;
            case 9: daysInMonth = 30;
                break;
            case 10: daysInMonth = 31;
                break;
            case 11: daysInMonth = 30;
                break;
            case 12: daysInMonth = 31;
                break;
        }

        if (Month >= 1 && Month <= 12)
        {
            monthValid = "yes";
        }
        else
        {
            monthValid = "no";
        }
        if (Month == 2)
        {
            if (leap == "no")
            {
                daysInMonth = 28;
            }
            else if (leap == "yes")
            {
                daysInMonth = 29;
            }
        }

        if (1 <= Day && Day <= daysInMonth)
        {
            dayValid = "yes";
        }

        else
        {
            dayValid = "no";
        }

        if ((dayValid == "yes") && (monthValid == "yes") && (yearValid == "yes"))
        {
            _isValid = true;
        }
        else
        {
            _isValid = false;
        }

        return _isValid;
    }

    public Int16 SelectDateValid(DateTime TodayDate, DateTime StartDate, DateTime EndDate, Int16 ReportType)
    {
        try
        {
            Int16 _result = 0;
            if (ReportType == 0)
            {
                if (StartDate > TodayDate)
                {
                    _result = 5;
                }
            }
            else if (ReportType == 1)
            {
                if (StartDate >= TodayDate)
                {
                    _result = 1;
                }
            }
            else if (ReportType == 2)
            {
                if (StartDate >= TodayDate)
                {
                    _result = 2;
                }
                else if (EndDate >= TodayDate)
                {
                    _result = 3;
                }
                else if (StartDate == EndDate)
                {
                    _result = 4;
                }
            }
            return _result;
        }
        catch (Exception ex)
        {
            return 10;
        }
    }

    public string GetMonth(string month)
    {
        string result = "";
        if (month == "01")
        {
            result = " Jan ";
        }
        else if (month == "02")
        {
            result = " Feb ";
        }
        else if (month == "03")
        {
            result = " Mar ";
        }
        else if (month == "04")
        {
            result = " Apr ";
        }
        else if (month == "05")
        {
            result = " May ";
        }
        else if (month == "06")
        {
            result = " Jun ";
        }
        else if (month == "07")
        {
            result = " Jul ";
        }
        else if (month == "08")
        {
            result = " Aug ";
        }
        else if (month == "09")
        {
            result = " Sep ";
        }
        else if (month == "10")
        {
            result = " Oct ";
        }
        else if (month == "11")
        {
            result = " Nov ";
        }
        else if (month == "12")
        {
            result = " Dec ";
        }
        return result;
    }

    public bool Validatioin()
    {
        lblError.Text = "";
        bool _isValid = true;
        DateTime _fromDate, _toDate;
        if (Convert.ToInt16(ddlReportType.Text) == 1)
        {
            if (ddlDay1.SelectedIndex >= 0)
            {
                if (ValidDate(Convert.ToInt32(ddlYear1.Text), Convert.ToInt32(ddlMonth1.Text), Convert.ToInt32(ddlDay1.Text)))
                {
                    _fromDate = Convert.ToDateTime(Convert.ToInt32(ddlYear1.Text) + "/" + Convert.ToInt32(ddlMonth1.Text) + "/" + Convert.ToInt32(ddlDay1.Text));
                    if (_fromDate > CE.Global.General.GetUTCDateTime())
                    {
                        _isValid = false;
                        lblError.Text = "Date should be less than or equal to Today";
                    }
                }
                else
                {
                    _isValid = false;
                    lblError.Text = "Select Valid Date";
                }
            }
            else
            {
                _isValid = false;
                lblError.Text = "Select Date";
            }
        }
        else if (Convert.ToInt16(ddlReportType.Text) == 2)
        {
            if (ddlDay1.SelectedIndex >= 0 && ddlDay2.SelectedIndex >= 0)
            {
                if (ValidDate(Convert.ToInt32(ddlYear1.Text), Convert.ToInt32(ddlMonth1.Text), Convert.ToInt32(ddlDay1.Text)))
                {
                    _fromDate = Convert.ToDateTime(Convert.ToInt32(ddlYear1.Text) + "/" + Convert.ToInt32(ddlMonth1.Text) + "/" + Convert.ToInt32(ddlDay1.Text));
                    if (ValidDate(Convert.ToInt32(ddlYear2.Text), Convert.ToInt32(ddlMonth2.Text), Convert.ToInt32(ddlDay2.Text)))
                    {
                        _toDate = Convert.ToDateTime(Convert.ToInt32(ddlYear2.Text) + "/" + Convert.ToInt32(ddlMonth2.Text) + "/" + Convert.ToInt32(ddlDay2.Text));
                        if (_fromDate <= CE.Global.General.GetUTCDateTime())
                        {
                            if (_fromDate > _toDate)
                            {
                                _isValid = false;
                                lblError.Text = "From date should be less than To date";
                            }
                            else
                            {
                                TimeSpan span = _toDate.Subtract(_fromDate);
                                if (span.Days > 31)
                                {
                                    _isValid = false;
                                    lblError.Text = "Number of Days Should not be Exceed 31 days";
                                }
                            }
                        }
                        else
                        {
                            _isValid = false;
                            lblError.Text = "From date should be less than Today";
                        }
                    }
                    else
                    {
                        _isValid = false;
                        lblError.Text = "Select Valid To date";
                    }
                }
                else
                {
                    _isValid = false;
                    lblError.Text = "Select Valid From date";
                }
            }
            else
            {
                _isValid = false;
                lblError.Text = "Select From date and To date";
            }
        }
        return _isValid;
    }

    public void LoadGrid()
    {
        lblError.Text = "";
        if (Validatioin() == true)
        {
            Int16 _reportType = Convert.ToInt16(ddlReportType.Text);

            Int32 _tDay = General.GetUTCDateTime().Day;
            Int32 _tMonth = General.GetUTCDateTime().Month;
            Int32 _tYear = General.GetUTCDateTime().Year;
            DateTime _todaydate = Convert.ToDateTime(_tYear + "/" + _tMonth + "/" + _tDay);

            Int32 _sDay = Convert.ToInt32(ddlDay1.Text);
            Int32 _sMonth = Convert.ToInt32(ddlMonth1.Text);
            Int32 _sYear = Convert.ToInt32(ddlYear1.Text);
            DateTime _startdate = Convert.ToDateTime(_sYear + "/" + _sMonth + "/" + _sDay);

            Int32 _eDay = Convert.ToInt32(ddlDay2.Text);
            Int32 _eMonth = Convert.ToInt32(ddlMonth2.Text);
            Int32 _eYear = Convert.ToInt32(ddlYear2.Text);
            DateTime _enddate = Convert.ToDateTime(_eYear + "/" + _eMonth + "/" + _eDay);

            Int16 _ValidResult = SelectDateValid(_todaydate, _startdate, _enddate, _reportType);
            if (_ValidResult == 1)
            {
                lblError.Text = "Select Date less than Today Date";
                return;
            }
            else if (_ValidResult == 2)
            {
                lblError.Text = "Select From Date less than Today Date";
                return;
            }
            else if (_ValidResult == 3)
            {
                lblError.Text = "Select To Date less than Today Date";
                return;
            }
            else if (_ValidResult == 4)
            {
                lblError.Text = "Select From Date less than To Date";
                return;
            }
            else if (_ValidResult == 5)
            {
                lblError.Text = "Selected Month should be less than or Equal to Current month";
                return;
            }
            else if (_ValidResult == 10)
            {
                lblError.Text = "Please try again later";
                return;
            }

            DateTime d = _startdate;
            DateTime _dtmstartDate = d;
            DateTime _dtmendDate = _enddate;

            txtReportDate2.Text = _dtmendDate.Day.ToString("00") + "/" + _dtmendDate.Month.ToString("00") + "/" + _dtmendDate.Year.ToString("0000");
            if (_reportType == 1)
            {
                txtReportDate2.Text = _dtmstartDate.Day.ToString("00") + "/" + _dtmstartDate.Month.ToString("00") + "/" + _dtmstartDate.Year.ToString("0000");
            }

            if (_startdate.DayOfWeek == DayOfWeek.Monday)
            {
                _dtmstartDate = d.AddDays(-3);
            }
            else
            {
                _dtmstartDate = d.AddDays(-1);
            }

            txtReportDate1.Text = _dtmstartDate.Day.ToString("00") + "/" + _dtmstartDate.Month.ToString("00") + "/" + _dtmstartDate.Year.ToString("0000");

            DataSet _dsSummary = new DataSet();
            Int16 _ReportType = Convert.ToInt16(ddlReportType.Text);
            string _ReportDate1 = txtReportDate1.Text.Trim();
            string _ReportDate2 = txtReportDate2.Text.Trim();

            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "sprocCXTHApplicationStatusReportSelectedList";
            SortedList _sPara = new SortedList();
            if (_reportType == 1)
            {
                _sPara.Add("@ApplicationStartDate", _ReportDate1);
                _sPara.Add("@ApplicationEndDate", _ReportDate2);
            }
            else
            {
                _sPara.Add("@ReportType", _ReportType);
                _sPara.Add("@ApplicationStartDate", _ReportDate1);
                _sPara.Add("@ApplicationEndDate", _ReportDate2);
            }

            grvSalesList.Visible = true;
            _dsSummary = _db.GetDataSet(_spName, _sPara);
            if (_dsSummary != null && _dsSummary.Tables.Count > 12)
            {
                DataTable _dtSummary = new DataTable();
                _dtSummary = GetTopNRows(_dsSummary.Tables[12], 13);
                lblError.Text = "";
                grvSalesList.DataSource = _dtSummary;
                grvSalesList.DataBind();
            }
            else
            {
                grvSalesList.DataSource = null;
                grvSalesList.DataBind();
            }
        }
        else
        {
            grvSalesList.DataSource = null;
            grvSalesList.DataBind();
        }
    }

    public DataTable GetTopNRows(DataTable dt, int n)
    {
        DataTable result = dt.Clone();
        for (int i = 0; i < n; i++)
        {
            result.ImportRow(dt.Rows[i]);
        }
        return result;
    }

    private void CheckPageAccess()
    {
        Int64 PageAccess = General.GetPageAccess(Convert.ToInt64(Session[General.UserId].ToString()), PageCode);
        if (PageAccess == 0)
        {
            Response.Redirect("~/UserMessage.aspx?menu=0&mt=UM&ec=101");
        }
        else
        {
            EnablePageAccess(PageAccess);
        }
    }

    private void EnablePageAccess(Int64 PageAccess)
    {
        if (PageAccess == 100)
        {
            //btnDownLoad.Enabled = false;
        }
        else if (PageAccess == 200)
        {
            //btnDownLoad.Enabled = true;
        }
    }

    #region Test Purpose
    private void DateConversion()
    {
        int day = 1;
        int month = 3;
        int year = 2014;
        int hour = 9;
        int minute = 30;
        string period = "AM";
        DateTime _dtdate = General.GetUTCDateTime();
        if (period == "AM")
        {
            string _dateTime = day.ToString("00") + "/" + month.ToString("00") + "/" + year + " " + hour.ToString("00") + ":" + minute.ToString("00") + ":00 " + period; 
            string sysFormat = "dd/MM/yyyy hh:mm:ss tt";
            DateTime dt = DateTime.ParseExact(_dateTime, sysFormat, CultureInfo.InvariantCulture);
            _dtdate = dt.AddHours(-13);
            lblError.Text = _dtdate.ToString(sysFormat);
        }
    }

    private void GenerateExcel()
    {
        try
        {
            Int16 ReportType = 0;
            DateTime d = General.GetUTCDateTime();
            if (d.DayOfWeek == DayOfWeek.Friday)
            {
                ReportType = 2;
            }
            DateTime _dtmstartApplicationDate = General.GetUTCDateTime();
            DateTime _dtmendApplicationDate = d;
            //ReportType = 0;
            if (ReportType == 2)
            {
                _dtmstartApplicationDate = d.AddDays(-7);
            }
            else
            {
                _dtmstartApplicationDate = d.AddDays(-1);
                _dtmendApplicationDate = d.AddDays(-1);
            }

            string _strstartApplicationDate = "";
            string _strendApplicationDate = "";
            _strstartApplicationDate = _dtmstartApplicationDate.Day.ToString("00") + "/" + _dtmstartApplicationDate.Month.ToString("00") + "/" + _dtmstartApplicationDate.Year.ToString("0000");
            _strendApplicationDate = _dtmendApplicationDate.Day.ToString("00") + "/" + _dtmendApplicationDate.Month.ToString("00") + "/" + _dtmendApplicationDate.Year.ToString("0000");


            #region Variables
            string _strNewTotal = "0";
            string _strUncontactTotal = "0";
            string _strPolicyNotTotal = "0";
            string _strReAssignUNTotal = "0";
            string _strOldATotal = "0";
            string _strOldBTotal = "0";
            string _strReAssignBRleftTtoal = "0";
            string _strReAssign15DaysTtoal = "0";
            string _strCallMeTtoal = "0";
            string _strCallDirectGGTtoal = "0";
            string _strCallDirectNonGGTtoal = "0";
            string _strNewRenewalTtoal = "0";
            string _strDailyVMITotal = "0";
            string _strMonthlyVMITotal = "0";
            string _strDailywithCMITotal = "0";
            string _strMonthlywithCMITotal = "0";

            decimal _dNewTotal = 0;
            decimal _dUncontactTotal = 0;
            decimal _dPolicyNotTotal = 0;
            decimal _dReAssignUNTotal = 0;
            decimal _dOldATotal = 0;
            decimal _dOldBTotal = 0;
            decimal _dReAssignBRleftTtoal = 0;
            decimal _dReAssign15DaysTtoal = 0;
            decimal _dCallMeTtoal = 0;
            decimal _dCallDirectGGTtoal = 0;
            decimal _dCallDirectNonGGTtoal = 0;
            decimal _dNewRenewalTotal = 0;
            decimal _dDailyVMITotal = 0;
            decimal _dMonthlyVMITotal = 0;
            decimal _dDailywithCMITotal = 0;
            decimal _dMonthlywithCMITotal = 0;

            string _emailBody = "";
            Int32 _NewCount = 0;
            #endregion

            DataSet _dsNewApplicationStatus = new DataSet();
            MyDatabase _db = new MyDatabase("GGR_DBConnectionString");
            string _spName = "sprocCXTHApplicationStatusReportSelectedList";

            SortedList _spara = new SortedList();
            _spara.Add("@ApplicationStartDate", _strstartApplicationDate);
            _spara.Add("@ApplicationEndDate", _strendApplicationDate);
            _spara.Add("@ReportType", ReportType);


            using (DataSet _ds = _db.GetDataSet(_spName, _spara))
            {
                if (_ds != null && _ds.Tables.Count > 0 && _ds.Tables[0].Rows.Count > 0)
                {
                    _dsNewApplicationStatus = _ds;
                }
            }

            if (_dsNewApplicationStatus != null && _dsNewApplicationStatus.Tables.Count > 0)
            {
                #region Get Count
                if (_dsNewApplicationStatus.Tables.Count > 0 && _dsNewApplicationStatus.Tables[0].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[0].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 1 && _dsNewApplicationStatus.Tables[1].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[1].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 2 && _dsNewApplicationStatus.Tables[2].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[2].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 3 && _dsNewApplicationStatus.Tables[3].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[3].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 4 && _dsNewApplicationStatus.Tables[4].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[4].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 5 && _dsNewApplicationStatus.Tables[5].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[5].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 6 && _dsNewApplicationStatus.Tables[6].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[6].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 7 && _dsNewApplicationStatus.Tables[7].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[7].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 8 && _dsNewApplicationStatus.Tables[8].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[8].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 9 && _dsNewApplicationStatus.Tables[9].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[9].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 10 && _dsNewApplicationStatus.Tables[10].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[10].Rows.Count;
                }
                if (_dsNewApplicationStatus.Tables.Count > 11 && _dsNewApplicationStatus.Tables[11].Rows.Count > 0)
                {
                    _NewCount += _dsNewApplicationStatus.Tables[11].Rows.Count;
                }
                #endregion

                #region Daily and Monthly Total Collection - Values
                if (_dsNewApplicationStatus.Tables.Count > 12 && _dsNewApplicationStatus.Tables[12].Rows.Count > 0)
                {
                    if (_dsNewApplicationStatus.Tables[12].Rows[0]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[0]["Amount"].ToString() != string.Empty)
                    {
                        _strNewTotal = _dsNewApplicationStatus.Tables[12].Rows[0]["Amount"].ToString();
                        if (!decimal.TryParse(_strNewTotal, out _dNewTotal))
                        {
                            _dNewTotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 1 && _dsNewApplicationStatus.Tables[12].Rows[1]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[1]["Amount"].ToString() != string.Empty)
                    {
                        _strUncontactTotal = _dsNewApplicationStatus.Tables[12].Rows[1]["Amount"].ToString();
                        if (!decimal.TryParse(_strUncontactTotal, out _dUncontactTotal))
                        {
                            _dUncontactTotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 2 && _dsNewApplicationStatus.Tables[12].Rows[2]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[2]["Amount"].ToString() != string.Empty)
                    {
                        _strPolicyNotTotal = _dsNewApplicationStatus.Tables[12].Rows[2]["Amount"].ToString();
                        if (!decimal.TryParse(_strPolicyNotTotal, out _dPolicyNotTotal))
                        {
                            _dPolicyNotTotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 3 && _dsNewApplicationStatus.Tables[12].Rows[3]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[3]["Amount"].ToString() != string.Empty)
                    {
                        _strReAssignUNTotal = _dsNewApplicationStatus.Tables[12].Rows[3]["Amount"].ToString();
                        if (!decimal.TryParse(_strReAssignUNTotal, out _dReAssignUNTotal))
                        {
                            _dReAssignUNTotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 4 && _dsNewApplicationStatus.Tables[12].Rows[4]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[3]["Amount"].ToString() != string.Empty)
                    {
                        _strOldATotal = _dsNewApplicationStatus.Tables[12].Rows[4]["Amount"].ToString();
                        if (!decimal.TryParse(_strOldATotal, out _dOldATotal))
                        {
                            _dOldATotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 5 && _dsNewApplicationStatus.Tables[12].Rows[5]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[5]["Amount"].ToString() != string.Empty)
                    {
                        _strOldBTotal = _dsNewApplicationStatus.Tables[12].Rows[5]["Amount"].ToString();
                        if (!decimal.TryParse(_strOldBTotal, out _dOldBTotal))
                        {
                            _dOldBTotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 6 && _dsNewApplicationStatus.Tables[12].Rows[6]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[6]["Amount"].ToString() != string.Empty)
                    {
                        _strReAssignBRleftTtoal = _dsNewApplicationStatus.Tables[12].Rows[6]["Amount"].ToString();
                        if (!decimal.TryParse(_strReAssignBRleftTtoal, out _dReAssignBRleftTtoal))
                        {
                            _dReAssignBRleftTtoal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 7 && _dsNewApplicationStatus.Tables[12].Rows[7]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[7]["Amount"].ToString() != string.Empty)
                    {
                        _strReAssign15DaysTtoal = _dsNewApplicationStatus.Tables[12].Rows[7]["Amount"].ToString();
                        if (!decimal.TryParse(_strReAssign15DaysTtoal, out _dReAssign15DaysTtoal))
                        {
                            _dReAssign15DaysTtoal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 8 && _dsNewApplicationStatus.Tables[12].Rows[8]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[8]["Amount"].ToString() != string.Empty)
                    {
                        _strCallMeTtoal = _dsNewApplicationStatus.Tables[12].Rows[8]["Amount"].ToString();
                        if (!decimal.TryParse(_strCallMeTtoal, out _dCallMeTtoal))
                        {
                            _dCallMeTtoal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 9 && _dsNewApplicationStatus.Tables[12].Rows[9]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[9]["Amount"].ToString() != string.Empty)
                    {
                        _strCallDirectGGTtoal = _dsNewApplicationStatus.Tables[12].Rows[9]["Amount"].ToString();
                        if (!decimal.TryParse(_strCallDirectGGTtoal, out _dCallDirectGGTtoal))
                        {
                            _dCallDirectGGTtoal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 10 && _dsNewApplicationStatus.Tables[12].Rows[10]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[10]["Amount"].ToString() != string.Empty)
                    {
                        _strCallDirectNonGGTtoal = _dsNewApplicationStatus.Tables[12].Rows[10]["Amount"].ToString();
                        if (!decimal.TryParse(_strCallDirectNonGGTtoal, out _dCallDirectNonGGTtoal))
                        {
                            _dCallDirectNonGGTtoal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 11 && _dsNewApplicationStatus.Tables[12].Rows[11]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[11]["Amount"].ToString() != string.Empty)
                    {
                        _strNewRenewalTtoal = _dsNewApplicationStatus.Tables[12].Rows[11]["Amount"].ToString();
                        if (!decimal.TryParse(_strNewRenewalTtoal, out _dNewRenewalTotal))
                        {
                            _dNewRenewalTotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 12 && _dsNewApplicationStatus.Tables[12].Rows[12]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[12]["Amount"].ToString() != string.Empty)
                    {
                        _strDailyVMITotal = _dsNewApplicationStatus.Tables[12].Rows[12]["Amount"].ToString();
                        if (!decimal.TryParse(_strDailyVMITotal, out _dDailyVMITotal))
                        {
                            _dDailyVMITotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 13 && _dsNewApplicationStatus.Tables[12].Rows[13]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[13]["Amount"].ToString() != string.Empty)
                    {
                        _strDailywithCMITotal = _dsNewApplicationStatus.Tables[12].Rows[13]["Amount"].ToString();
                        if (!decimal.TryParse(_strDailywithCMITotal, out _dDailywithCMITotal))
                        {
                            _dDailywithCMITotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 14 && _dsNewApplicationStatus.Tables[12].Rows[14]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[14]["Amount"].ToString() != string.Empty)
                    {
                        _strMonthlyVMITotal = _dsNewApplicationStatus.Tables[12].Rows[14]["Amount"].ToString();
                        if (!decimal.TryParse(_strMonthlyVMITotal, out _dMonthlyVMITotal))
                        {
                            _dMonthlyVMITotal = 0;
                        }
                    }
                    if (_dsNewApplicationStatus.Tables[12].Rows.Count > 15 && _dsNewApplicationStatus.Tables[12].Rows[15]["Amount"] != DBNull.Value
                            && _dsNewApplicationStatus.Tables[12].Rows[15]["Amount"].ToString() != string.Empty)
                    {
                        _strMonthlywithCMITotal = _dsNewApplicationStatus.Tables[12].Rows[15]["Amount"].ToString();
                        if (!decimal.TryParse(_strMonthlywithCMITotal, out _dMonthlywithCMITotal))
                        {
                            _dMonthlywithCMITotal = 0;
                        }
                    }
                }
                #endregion

                if (_NewCount > 0)
                {
                    #region Email Body
                    _emailBody = "";
                    _emailBody += "<html>";
                    _emailBody += "<head>";
                    _emailBody += "<title></title>";
                    _emailBody += "</head>";
                    _emailBody += "<body class='email_body'>";

                    _emailBody += "<style type='text/css'>";
                    _emailBody += ".email_body {background: white;margin: 0;padding: 0;color: #4c4c4c;font-family: Arial, Helvetica, sans-serif;font-size: 0.85em;}";
                    _emailBody += "a {text-decoration:underline;color:#2c95cc;} ";
                    _emailBody += "#email_container {margin:0 auto;padding:20px;width:600px; height:auto;} ";
                    _emailBody += "#email_body {width:100%; text-align: left !important;} ";
                    _emailBody += ".email_pad10px {padding:10px;} ";
                    _emailBody += ".email_results td {width:33%;} ";
                    _emailBody += "</style>";

                    //_emailBody += "<table width='100%' border='0' cellspacing='2' cellpadding='0'>";
                    //_emailBody += "<tr><td style='color:green; font-size:large;'><b>GG Sales at " + _dtmApplicationDate.ToString("dd/MMM/yyyy dddd") + "</b></td></tr>";
                    //_emailBody += "<tr><td>&nbsp;</td></tr>";

                    #region NEW (Fresh Leads)
                    if (_dsNewApplicationStatus.Tables[0].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>NEW (Fresh Leads)</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[0].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[0].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[0].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[0].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[0].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[0].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dNewTotal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region UN-Contacted Leads
                    if (_dsNewApplicationStatus.Tables[1].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>UN-Contacted Sales</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[1].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[1].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[1].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[1].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[1].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[1].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dUncontactTotal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Re-assign Policy Not Expire Soon
                    if (_dsNewApplicationStatus.Tables[2].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Re-assign Policy Not Expire Soon</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[2].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[2].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[2].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[2].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[2].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[2].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dPolicyNotTotal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Re-assign Un-Contact Leads
                    if (_dsNewApplicationStatus.Tables[3].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Re-assign Un-Contacted Sales</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[2].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[3].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[3].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[3].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[3].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[3].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dReAssignUNTotal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Old Leads - A
                    if (_dsNewApplicationStatus.Tables[4].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Old Leads (A) Sales</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[4].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[4].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[4].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[4].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[4].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[4].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dOldATotal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Old Leads - B
                    if (_dsNewApplicationStatus.Tables[5].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Old Leads (B) Sales</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[5].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[5].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[5].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[5].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[5].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[5].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dOldBTotal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Re-assign Call Backs (Broker - Who Left)
                    if (_dsNewApplicationStatus.Tables[6].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Re-assign Call Backs (Broker - Who Left)</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[6].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[6].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[6].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[6].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[6].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[6].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dReAssignBRleftTtoal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Re-assign Call Backs (Not Completed in 15 working days)
                    if (_dsNewApplicationStatus.Tables[7].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Re-assign Call Backs (Not Completed in 15 working days)</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[7].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[7].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[7].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[7].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[7].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[7].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dReAssign15DaysTtoal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Call Me
                    if (_dsNewApplicationStatus.Tables[8].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Call Me Sales</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[8].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[8].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[8].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[8].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[8].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[8].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dCallMeTtoal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Call Direct (GG Customer)
                    if (_dsNewApplicationStatus.Tables[9].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Call Direct (GG Customer)</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[9].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[9].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[9].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[9].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[9].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[9].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dCallDirectGGTtoal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Call Direct (Not visit to GG before Call)
                    if (_dsNewApplicationStatus.Tables[10].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Call Direct (Not visit to GG before Call)</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[10].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[10].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[10].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[10].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[10].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[10].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dCallDirectNonGGTtoal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Renewal Leads
                    if (_dsNewApplicationStatus.Tables[11].Rows.Count > 0)
                    {
                        _emailBody += "<tr><td>";
                        _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";

                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='4' style='background: #E6E6E6;'><b>Renewal Sales</b></td>";
                        _emailBody += "</tr>";

                        _emailBody += "<tr>";
                        _emailBody += "<td><b>No.</b></td>";
                        _emailBody += "<td><b>InsuranceType</b></td>";
                        _emailBody += "<td><b>CompanyName</b></td>";
                        _emailBody += "<td align='right'><b>TotalPremium</b></td>";
                        _emailBody += "</tr>";
                        int _No = 0;
                        for (int i = 0; i < _dsNewApplicationStatus.Tables[11].Rows.Count; i++)
                        {
                            _No += 1;
                            float _TotalPreium = 0.0f;
                            if (_dsNewApplicationStatus.Tables[11].Rows[i]["TotalCollectedPremium"] != DBNull.Value && _dsNewApplicationStatus.Tables[11].Rows[i]["TotalCollectedPremium"].ToString() != string.Empty)
                                _TotalPreium = Convert.ToSingle(_dsNewApplicationStatus.Tables[11].Rows[i]["TotalCollectedPremium"].ToString());
                            _emailBody += "<tr>";
                            _emailBody += "<td>" + _No.ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[11].Rows[i]["InsuranceType"].ToString() + "</td>";
                            _emailBody += "<td>" + _dsNewApplicationStatus.Tables[11].Rows[i]["CompanyName"].ToString() + "</td>";
                            _emailBody += "<td align='right'>" + _TotalPreium.ToString("#,#0.00") + "</td>";
                            _emailBody += "</tr>";
                        }
                        _emailBody += "<tr>";
                        _emailBody += "<td colspan='3' align='right'><b>Grand Total</b></td>";
                        _emailBody += "<td width='150' align='right'><b>" + _dNewRenewalTotal.ToString("#,#0.00") + "</b></td>";
                        _emailBody += "</tr>";
                        _emailBody += "</table>";
                        _emailBody += "</td></tr>";
                        _emailBody += "<tr><td colspan='4'>&nbsp;</td></tr>";
                    }
                    #endregion

                    #region Daily and Monthly Total Collection
                    _emailBody += "<tr><td>";
                    _emailBody += "<table width='100%' border='1' cellspacing='2' cellpadding='0'>";
                    _emailBody += "<tr>";
                    //_emailBody += "<td colspan='2' style='background: #E6E6E6;'><b>Total Collection</b></td>";
                    _emailBody += "<td colspan='4' style='background-color:#E6E6E6;'><b>No.of Sales:" + _NewCount.ToString() + "</b></td>";
                    _emailBody += "</tr>";

                    _emailBody += "<tr>";
                    //_emailBody += "<td><b></b></td>";
                    _emailBody += "<td colspan='2'><b>Description</b></td>";
                    _emailBody += "<td align='right'><b>VMI Only</b></td>";
                    _emailBody += "<td align='right'><b>VMI + CMI</b></td>";
                    _emailBody += "</tr>";

                    _emailBody += "<tr>";
                    //_emailBody += "<td style='color:#045FB4;'></td>";
                    _emailBody += "<td colspan='2' style='color:#045FB4;'><b>Total Collection</td>";
                    if (_dDailyVMITotal >= 1000)
                    {
                        _emailBody += "<td style='color:#045FB4;' align='right'>" + _dDailyVMITotal.ToString("#0,000.00") + "</td>";
                    }
                    else
                    {
                        _emailBody += "<td style='color:#045FB4;' align='right'>" + _dDailyVMITotal.ToString("#0.00") + "</td>";
                    }
                    if (_dDailywithCMITotal >= 1000)
                    {
                        _emailBody += "<td  style='color:#045FB4;' align='right'>" + _dDailywithCMITotal.ToString("#0,000.00") + "</td>";
                    }
                    else
                    {
                        _emailBody += "<td  style='color:#045FB4;' align='right'>" + _dDailywithCMITotal.ToString("#0.00") + "</td>";
                    }
                    _emailBody += "</tr>";
                    #endregion

                    _emailBody += "</table>";
                    _emailBody += "</body>";
                    _emailBody += "</html>";
                    #endregion
                    Label lblContent = new Label();
                    lblContent.Text = _emailBody;
                    ExportGridView("Sales", lblContent);
                }
            }
        }
        catch (Exception ex)
        {
        }
    }

    public void ExportGridView(string fileName, Label lblContent)
    {
        string attachment = "attachment; filename=" + fileName + ".xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/ms-excel";
        Response.Write(lblContent.Text);
        Response.End();
    }
    #endregion

}
